package com.alibaba.easyexcel.test.demo.iting;

import com.alibaba.easyexcel.test.demo.write.IndexData;
import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.junit.Test;

import java.io.File;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;

public class CountDemo {
    private static final String BASE_PATH = "D:\\easyExcelDemo\\";
    private Random random = new Random();
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
    private static final SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy年MM月dd日");

    /**
     * 根据模板写入
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link IndexData}
     * <p>
     * 2. 使用{@link ExcelProperty}注解指定写入的列
     * <p>
     * 3. 使用withTemplate 写取模板
     * <p>
     * 4. 直接写即可
     */
    @Test
    public void templateWrite() {
        String templateFileName =  TestFileUtil.getPath() + "demo" + File.separator + "count1.xlsx";
        String fileName =  TestFileUtil.getPath() + "demo" + File.separator + "count1two.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(fileName);
        ExcelWriterBuilder excelWriterBuilderTemplate = excelWriterBuilder.withTemplate(templateFileName);
        ExcelWriterSheetBuilder sheet0 = excelWriterBuilderTemplate.sheet(0);
        ExcelWriterSheetBuilder sheet1 = excelWriterBuilderTemplate.sheet(1);
        sheet0.doWrite(teampP());
        sheet1.doWrite(teampA());
    }

    /**
     * 复杂的填充
     *
     * @since 2.1.1
     */
    @Test
    public void complexFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量

        String templateFileName =  TestFileUtil.getPath() + "demo" + File.separator + "count1.xlsx";
        String fileName =  TestFileUtil.getPath() + "demo" + File.separator + "count1two.xlsx";
        Date date = new Date();
        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
        WriteSheet writeSheet0 = EasyExcel.writerSheet(0).build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet(1).build();
        WriteSheet writeSheet2 = EasyExcel.writerSheet(2).build();

        excelWriter.write(teampP(), writeSheet0);
        excelWriter.write(teampA(), writeSheet1);
        excelWriter.write(teampM(), writeSheet2);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", simpleDateFormat2.format(date));
        map.put("disName", "聂志鹏");
        map.put("disCode", "123456");
        excelWriter.fill(map, writeSheet0);

        excelWriter.finish();
    }

    private ArrayList teampM() {
        ArrayList<CountItemM> countItemMS = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            CountItemM countItemM = new CountItemM();
            countItemM.setInvestorsNumber(new BigDecimal(random.nextInt(100000000)));
            countItemM.setHoldBalance(new BigDecimal(random.nextInt(100000000)));
            String[] representItems = CountItemM.representItems;
            countItemM.setRepresent(representItems[i % representItems.length]);
            int nextInt1 = random.nextInt(100000000);
            String s1 = "000000" + nextInt1;
            s1 = s1.substring(s1.length() -6);
            countItemM.setDisCodeM(s1);
            countItemM.setNetSalesAmount(new BigDecimal(random.nextInt(100000000)));
            int nextInt = random.nextInt(100000000);
            String s = "000000" + nextInt;
            s = s.substring(s.length() -6);
            countItemM.setProdRegisterCode(s);
            countItemM.setTotalSalesAmount(new BigDecimal(random.nextInt(100000000)));
            countItemMS.add(countItemM);
        }
        return countItemMS;
    }

    private ArrayList teampA() {
        ArrayList<CountItemA> countItemAS = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            CountItemA countItemA = new CountItemA();
            countItemA.setInvestorsNumber(new BigDecimal(random.nextInt(100000000)));
            countItemA.setHoldBalance(new BigDecimal(random.nextInt(100000000)));
            String[] regionTypeItems = CountItemA.regionTypeItems;
            countItemA.setRegionType(regionTypeItems[i % regionTypeItems.length]);
            countItemA.setNetSalesAmount(new BigDecimal(random.nextInt(100000000)));
            int nextInt = random.nextInt(100000000);
            String s = "000000" + nextInt;
            s = s.substring(s.length() -6);
            countItemA.setProdRegisterCode(s);
            countItemA.setTotalSalesAmount(new BigDecimal(random.nextInt(100000000)));
            countItemAS.add(countItemA);
        }
        return countItemAS;
    }

    private ArrayList teampP() {
        ArrayList<CountItemP> countItemPS = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            CountItemP countItemP = new CountItemP();
            countItemP.setInvestorsNumber(new BigDecimal(random.nextInt(100000000)));
            countItemP.setHoldBalance(new BigDecimal(random.nextInt(100000000)));
            String[] investorTypeItems = CountItemP.investorTypeItems;
            countItemP.setInvestorType(investorTypeItems[i % investorTypeItems.length]);
            countItemP.setNetSalesAmount(new BigDecimal(random.nextInt(100000000)));
            int nextInt = random.nextInt(100000000);
            String s = "000000" + nextInt;
            s = s.substring(s.length() -6);
            countItemP.setProdRegisterCode(s);
            countItemP.setTotalSalesAmount(new BigDecimal(random.nextInt(100000000)));
            countItemPS.add(countItemP);
        }
        return countItemPS;
    }
}
